package com.elitel.frame.business.service.workflow;

import com.elitel.common.businessJdbc.BusinessSelectPreparedStatement;
import com.elitel.common.businessJdbc.BusinessSelectPreparedStatementHandler;
import com.elitel.common.utils.LogTraceUtil;
import com.elitel.common.utils.LogTraceUtil.LogTraceTask;
import com.elitel.common.utils.LogTraceUtil.LogTracer;
import com.elitel.common.utils.XCommonUtils;
import com.elitel.frame.base.component.RedisCacheComponent;
import com.elitel.frame.business.service.TableFieldCommService;
import com.elitel.frame.business.service.access.PrepareFilterSqlHandler;
import com.elitel.frame.core.util.ApplicationContextHelper;
import com.elitel.frame.main.entity.CfgDb;
import com.elitel.frame.main.entity.CfgExtend;
import com.elitel.frame.main.entity.CfgTable;
import com.elitel.frame.main.entity.constant.DictionaryConstant;
import com.elitel.frame.main.entity.ext.BaseQueryfieldExt;
import com.elitel.frame.main.entity.vo.PageResponseVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class SingleTableQueryWorkflow {

    @Autowired
    private RedisCacheComponent redisCacheComponent;

    @Autowired
    private TableFieldCommService tableFieldCommService;

    @Autowired
    @Qualifier("generalBusinessSelectPreparedStatementHandler")
    private BusinessSelectPreparedStatementHandler businessPrepareStatementHandler;

    public PageResponseVo<Map<String, Object>> querySingleTableList(CfgDb cfgDb, CfgExtend cfgExtend, String pageNO,
                                                                    String pageSize, Map<String, Object> passConditionMap) {
        PageResponseVo<Map<String, Object>> pageResponse = new PageResponseVo<Map<String, Object>>();

        LogTracer logTracer = LogTraceUtil.getLogTracer();
        LogTraceTask logTraceTask = logTracer.recordTask("prepare business statement relative resource in service ");

        List<Map<String, Object>> list = null;
        CfgTable cfgTable = null;
        Long count = 0l;// here need to become long type

        if (null == cfgExtend)
            return pageResponse;

        // 查询表名及数据源标识
        cfgTable = redisCacheComponent.cfgTableRedisCache(cfgExtend.getDtConfigId(), cfgExtend.getDbKey());

        if (null == cfgTable)
            return pageResponse;

        String sort = "";
        String tables = "";
        String fields = "";
        String whereSql = "";

        // 设置表名称
        tables = tableFieldCommService.getTablenameTxt(cfgDb, cfgExtend.getDtConfigId());
        // 设置显示字段
        fields = tableFieldCommService.getFieldnameByserviceID(cfgDb, cfgExtend.getGuid(), DictionaryConstant.ServiceType_SingleTableService + "", cfgExtend.getDtConfigId(), "");

        // 设置参数条件
        String sqltxt = tableFieldCommService.getQuerysqlByserviceId(cfgDb, cfgExtend.getGuid(), DictionaryConstant.ServiceType_SingleTableService + "", "");
        String wherecontent = tableFieldCommService.processSqlByFreemarker(cfgDb, sqltxt, passConditionMap);
        if (wherecontent != null && !wherecontent.isEmpty()) {
            whereSql += wherecontent;
        }

        // 设置排序字段
        if (XCommonUtils.isNotEmpty(cfgTable.getDtOrder())) {
            sort = " order by " + (cfgTable.getDtTabletype() == 1 ? cfgTable.getDtOrder() : cfgTable.getDtOrder().replaceAll("_$", "."));
        }

        // here is get queryField object
        List<BaseQueryfieldExt> baseQueryFieldExtList = tableFieldCommService.getQueryFieldObjectByServiceIdAndServiceType(cfgDb, cfgExtend.getGuid(), DictionaryConstant.ServiceType_SingleTableService_String);
        Map<String, String> fieldOporMap = new HashMap<>();
        Map<String, String> fieldTypeMap = new HashMap<>();
        if (baseQueryFieldExtList != null) {
            baseQueryFieldExtList.forEach(baseQueryFieldExt -> fieldTypeMap.put(baseQueryFieldExt.getParametercode().trim(), baseQueryFieldExt.getFieldtype().trim()));
            baseQueryFieldExtList.forEach(e -> {
                if ("in".equalsIgnoreCase(e.getParametercondition().trim())) {
                    fieldOporMap.put(e.getParametercode().trim(), e.getParametercondition().trim());
                }
            });
        }
        if (!whereSql.isEmpty()) {
            String lowerCase = cfgDb.getDbVersion().toLowerCase().trim();
            Map<String, Object> map = dealTime(passConditionMap, lowerCase, whereSql, fieldTypeMap, fieldOporMap);
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                String key ="{{{"+ entry.getKey()+"}}}";
                Object value = entry.getValue();
                whereSql = whereSql.replace(key, value.toString());
            }

        }


        // here is for compare
        BusinessSelectPreparedStatement bps = BusinessSelectPreparedStatement.builder(cfgDb.getDbVersion())
                .serviceGuid(cfgExtend.getGuid())
                .fieldSection(fields)
                .tableSection(tables)
                .conditionSection(whereSql)
                .whereSql(whereSql)
                .orderSection(sort)
                .fieldTypeMap(fieldTypeMap)
                .passCondition(passConditionMap)
                .cfgDb(cfgDb)
                .isPageable(XCommonUtils.isPageable(cfgExtend.getIspagination()))
                .pageNumber(pageNO)
                .pageSize(pageSize)
                .filterSqlHandler(ApplicationContextHelper.getApplicationContext().getBean(PrepareFilterSqlHandler.class).handlerParameter())
                .build();

        logTraceTask.endTask();

        logTraceTask = logTracer.recordTask("businessPrepareStatementHandler execute find row list ");
        list = businessPrepareStatementHandler.findRowListOfFieldResultByExecuteSql(bps);
        logTraceTask.endTask();

//        if(bps.isPageable() && bps.getServiceGuid().equals("sqhs_project_singleTable_eBUo")){

        logTraceTask = logTracer.recordTask("businessPrepareStatementHandler execute find total count by where condition ");
        count = businessPrepareStatementHandler.rowCountByExecuteSql(bps);
        logTraceTask.endTask();
//        }

        pageResponse.setTotal((int) (long) count);
        pageResponse.setRows(list);

        return pageResponse;
    }

    private static Map<String, Object> dealTime(Map<String, Object> passConditionMap, String dbType,
                                                String wherecontent, Map<String, String> fieldTypeMap,
                                                Map<String, String> fieldOporMap) {
        Map<String, Object> map = new HashMap<>();
        if ("postgresql".equals(dbType)) {
            passConditionMap.forEach((k, v) -> {
                if (wherecontent.contains(k)) {
                    String fieldType = fieldTypeMap.get(k);
                    if (fieldOporMap.containsKey(k)) {
                        StringBuilder tmp = new StringBuilder();
                        if (fieldType != null && (fieldType.contains("time") || fieldType.contains("date"))) {
                            for (String s : v.toString().split(",")) {
                                tmp.append(",TO_TIMESTAMP('").append(s).append("', 'YYYY-MM-DD HH24:MI:SS')");
                            }
                            tmp = new StringBuilder("(" + tmp.substring(1) + ")");
                            map.put(k, tmp.toString());
                        } else if (fieldType != null && (fieldType.contains("varchar") || fieldType.contains("char"))) {
                            for (String s : v.toString().split(",")) {
                                tmp.append(",'").append(s).append("'");
                            }
                            tmp = new StringBuilder("(" + tmp.substring(1) + ")");
                            map.put(k, tmp.toString());
                        } else {
                            map.put(k, "(" + v + ")");
                        }

                    }else {
                        if (fieldType != null && (fieldType.contains("time") || fieldType.contains("date"))) {
                            String s = "TO_TIMESTAMP('" + v + "', 'YYYY-MM-DD HH24:MI:SS')";
                            map.put(k, s);
                        } else if (fieldType != null && (fieldType.contains("varchar") || fieldType.contains("char"))) {
                            map.put(k, "'" + v + "'");
                        } else {
                            map.put(k, v);
                        }
                    }
                }
            });
        } else if ("mysql".equals(dbType)) {
            passConditionMap.forEach((k, v) -> {
                if (wherecontent.contains(k)) {
                    String fieldType = fieldTypeMap.get(k);
                    if (fieldOporMap.containsKey(k)) {
                        StringBuilder tmp = new StringBuilder();
                        if (fieldType != null && (fieldType.contains("time") || fieldType.contains("date"))) {
                            for (String s : v.toString().split(",")) {
                                tmp.append(",STR_TO_DATE('").append(s).append("', '%Y-%m-%d %H:%i:%s')");
                            }
                            tmp = new StringBuilder("(" + tmp.substring(1) + ")");
                            map.put(k, tmp.toString());
                        } else if (fieldType != null && (fieldType.contains("varchar") || fieldType.contains("char"))) {
                            for (String s : v.toString().split(",")) {
                                tmp.append(",'").append(s).append("'");
                            }
                            tmp = new StringBuilder("(" + tmp.substring(1) + ")");
                            map.put(k, tmp.toString());
                        } else {
                            map.put(k, "(" + v + ")");
                        }

                    }else {
                        if (fieldType != null && (fieldType.contains("time") || fieldType.contains("date"))) {
                            String s = "STR_TO_DATE('" + v + "', '%Y-%m-%d %H:%i:%s')";
                            map.put(k, s);
                        } else if (fieldType != null && (fieldType.contains("varchar") || fieldType.contains("char"))) {
                            map.put(k, "'" + v + "'");
                        } else {
                            map.put(k, v);
                        }
                    }
                }
            });
        } else if ("oracle".equals(dbType)) {
            passConditionMap.forEach((k, v) -> {
                if (wherecontent.contains(k)) {
                    String fieldType = fieldTypeMap.get(k);
                    if (fieldOporMap.containsKey(k)) {
                        StringBuilder tmp = new StringBuilder();
                        if (fieldType != null && (fieldType.contains("time") || fieldType.contains("date"))) {
                            for (String s : v.toString().split(",")) {
                                tmp.append(",TO_TIMESTAMP('").append(s).append("', 'YYYY-MM-DD HH24:MI:SS')");
                            }
                            tmp = new StringBuilder("(" + tmp.substring(1) + ")");
                            map.put(k, tmp.toString());
                        } else if (fieldType != null && (fieldType.contains("varchar") || fieldType.contains("char"))) {
                            for (String s : v.toString().split(",")) {
                                tmp.append(",'").append(s).append("'");
                            }
                            tmp = new StringBuilder("(" + tmp.substring(1) + ")");
                            map.put(k, tmp.toString());
                        } else {
                            map.put(k, "(" + v + ")");
                        }

                    }else {
                        if (fieldType != null && (fieldType.contains("time") || fieldType.contains("date"))) {
                            String s = "TO_TIMESTAMP('" + v + "', 'YYYY-MM-DD HH24:MI:SS')";
                            map.put(k, s);
                        } else if (fieldType != null && (fieldType.contains("varchar") || fieldType.contains("char"))) {
                            map.put(k, "'" + v + "'");
                        } else {
                            map.put(k, v);
                        }
                    }
                }
            });
        } else if ("sqlserver".equals(dbType)) {
            passConditionMap.forEach((k, v) -> {
                if (wherecontent.contains(k)) {
                    String fieldType = fieldTypeMap.get(k);
                    if (fieldOporMap.containsKey(k)) {
                        StringBuilder tmp = new StringBuilder();
                        if (fieldType != null && (fieldType.contains("time") || fieldType.contains("date"))) {
                            for (String s : v.toString().split(",")) {
                                tmp.append(",CONVERT(DATETIME, '").append(s).append("', 120)");
                            }
                            tmp = new StringBuilder("(" + tmp.substring(1) + ")");
                            map.put(k, tmp.toString());
                        } else if (fieldType != null && (fieldType.contains("varchar") || fieldType.contains("char"))) {
                            for (String s : v.toString().split(",")) {
                                tmp.append(",'").append(s).append("'");
                            }
                            tmp = new StringBuilder("(" + tmp.substring(1) + ")");
                            map.put(k, tmp.toString());
                        } else {
                            map.put(k, "(" + v + ")");
                        }

                    }else {
                        if (fieldType != null && (fieldType.contains("time") || fieldType.contains("date"))) {
                            String s = "CONVERT(DATETIME, '" + v + "', 120)";
                            map.put(k, s);
                        } else if (fieldType != null && (fieldType.contains("varchar") || fieldType.contains("char"))) {
                            map.put(k, "'" + v + "'");
                        } else {
                            map.put(k, v);
                        }
                    }
                }
            });
        }
        return map;
    }
}
